Aakash Harpalani
Thursday, December 14, 2017
Loading libraries:
library(RPostgreSQL)
library(ggplot2)
library(lubridate)
library(dplyr)
Establishing connections and credentials:
drv <- dbDriver("PostgreSQL")
source("connect/connect.R")
Set intersection(s) of interest:
intersection_ids = seq(1,31,1)
Retrieve 15-min count data:
strSQL <-
paste0("SELECT datetime_bin, intersection_name, classification, leg, dir, volume ",
"FROM miovision.volumes_15min ",
"INNER JOIN miovision.classifications USING (classification_uid) ",
"INNER JOIN miovision.intersections USING (intersection_uid) ",
"ORDER BY datetime_bin, intersection_name, classification_uid, leg, dir ")
data <- dbGetQuery(con, strSQL)
Overall volumes, by date/tod
all_vehicles <- data %>%
group_by(datetime_bin, intersection_name, leg, dir) %>%
filter(classification %in% c('Lights','Single-Unit Trucks','Articulated Trucks','Buses')) %>%
summarize(total_volume = sum(volume))
head(all_vehicles)
## Source: local data frame [6 x 5]
## Groups: datetime_bin, intersection_name, leg [3]
##
## datetime_bin intersection_name leg dir total_volume
## <dttm> <chr> <chr> <chr> <int>
## 1 2017-10-03 13:15:00 King / Bathurst E EB 81
## 2 2017-10-03 13:15:00 King / Bathurst E WB 74
## 3 2017-10-03 13:15:00 King / Bathurst N NB 142
## 4 2017-10-03 13:15:00 King / Bathurst N SB 118
## 5 2017-10-03 13:15:00 King / Bathurst S NB 133
## 6 2017-10-03 13:15:00 King / Bathurst S SB 106
all_vehicles$dt <- floor_date(all_vehicles$datetime_bin,'1 day')
all_vehicles$time_bin <- as.POSIXct(paste(sprintf("%02d",hour(all_vehicles$datetime_bin)),sprintf("%02d",minute(all_vehicles$datetime_bin)),sep=":"), format = "%H:%M")
all_vehicles$wk <- strftime(all_vehicles$dt,format='%W')
all_vehicles$dow <- strftime(all_vehicles$dt, format = '%u')
Plot all direction/leg combinations (Adelaide and Bathurst):
for (intersection_id in intersection_ids){
strSQL <-
paste0("SELECT intersection_name FROM miovision.intersections WHERE intersection_uid = ",intersection_id)
intersection_name <- as.character(dbGetQuery(con, strSQL))
print(paste0(intersection_id,': ', intersection_name))
for (leg in c('N','S','W','E')){
for (dir in c('NB','SB','EB','WB')){
if (((leg %in% c('N','S')) && (dir %in% c('NB','SB'))) | ((leg %in% c('E','W')) && (dir %in% c('EB','WB')))){
subset <- all_vehicles[all_vehicles$dir == dir
& all_vehicles$leg == leg
& all_vehicles$intersection_name == intersection_name,]
dates <- data.frame(label = unique(all_vehicles$dt))
dates$dow <- strftime(dates$lab, format = '%u')
dates$wk <- strftime(dates$lab, format = '%W')
dates$x = as.POSIXct('04:00', format = '%H:%M')
dates$y = max(0.96 * max(subset$total_volume), 0.95)
print(ggplot(data = subset, aes(x=time_bin, y=total_volume)) +
geom_line(aes(color = ifelse(dow < 6, "weekday", "weekend")), size = 1) +
scale_colour_brewer(type = 'qua', palette = "Set1", direction = -1) +
scale_x_datetime(date_labels = "%H", date_breaks = "2 hours") +
facet_grid(dow ~ wk) +
geom_text(data = dates, aes(x,y-0.5,label=label)) +
theme_light() +
guides(color = F) +
theme(aspect.ratio = 1/2) +
labs(title = paste0(intersection_name,": ", leg, " leg, ", dir, " direction")),
width = 700, height = 1400)
}
}
}
}
## [1] "1: Adelaide / Bathurst"
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## [1] "2: Adelaide / Spadina"
## Warning in max(subset$total_volume): no non-missing arguments to max;
## returning -Inf
## Warning: Unknown or uninitialised column: 'PANEL'.
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## [1] "3: Adelaide / Bay"
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## [1] "4: Adelaide / Jarvis"
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## [1] "5: Front / Bathurst"
## Warning in max(subset$total_volume): no non-missing arguments to max;
## returning -Inf
## Warning: Unknown or uninitialised column: 'PANEL'.
## Warning: no non-missing arguments to max; returning -Inf
## Warning: Unknown or uninitialised column: 'PANEL'.
## [1] "6: Front / Spadina"
## [1] "7: Front / Bay"
## [1] "8: Front / Jarvis"
## [1] "9: King / Strachan"
## [1] "10: King / Bathurst"
## [1] "11: King / Portland"
## [1] "12: King / Spadina"
## [1] "13: King / Peter"
## [1] "14: King / Simcoe"
## [1] "15: King / University"
## [1] "16: King / York"
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## [1] "17: King / Bay"
## [1] "18: King / Yong"
## [1] "19: King / Church"
## [1] "20: King / Jarvis"
## [1] "21: King / Sherbourne"
## [1] "22: Queen / Bathurst"
## [1] "23: Queen / Spadina"
## [1] "24: Queen / Bay"
## [1] "25: Queen / Jarvis"
## [1] "26: Richmond / Bathurst"
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## [1] "27: Richmond / Spadina"
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## [1] "28: Richmond / Bay"
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## [1] "29: Richmond / Jarvis"
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## [1] "30: Wellington / Blue Jays"
## [1] "31: Wellington / Bay"
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?
## geom_path: Each group consists of only one observation. Do you need to
## adjust the group aesthetic?